package com.b511.swpu.cementbackend.repository;

import com.b511.swpu.cementbackend.dto.input.BoreholeConditionInputDto;
import com.b511.swpu.cementbackend.dto.input.WorkBaseInputDto;
import com.b511.swpu.cementbackend.util.DBHelper;
import com.b511.swpu.cementbackend.util.JSONHelper;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.springframework.data.domain.PageRequest;

import java.util.ArrayList;
import java.util.List;

public class WorkBaseRepository {
    //查询井口信息
    public JSONObject findWorkBase(WorkBaseInputDto workBaseInputDto, PageRequest pageRequest) throws Exception {
        DBHelper dbHelper=new DBHelper();

        StringBuilder totalSqlBuilder=new StringBuilder();
        totalSqlBuilder.append("select count(distinct BA_WELL.wellName) from BA_WELL where 1=1");

        StringBuilder sqlBuilder=new StringBuilder();
//        sqlBuilder.append("select * FROM BA_WELL where 1=1");
        sqlBuilder.append("select BA_WELL.wellName,BA_WELL.country,BA_WELL.area,BA_WELL.block,BA_WELL.oilfield,BA_WELL.geographicalPosition,BA_WELL.structuralLocation,\n" +
                "BA_WELL.waterDepth,BA_WELL.mudLine,BA_WELL.wellType,BA_WELL.wellSpacing,BA_WELL.cementCompany, TO_CHAR(BA_WELL.drillingStartTime,'YYYY-MM-DD') as drillingStartTime,\n" +
                "TO_CHAR(BA_WELL.drillingFinishTime,'YYYY-MM-DD') as drillingFinishTime,BA_WELL.actualWellDepth,BA_WELL.holeTemperature,BA_WELL.holePressure,BA_WELL.holeMaxDensity,BA_WELL.HoleMaxDensityCasingSize," +
                "BA_WELL.HoleMaxDensitySlurySystem,BA_WELL.HoleMaxDensityFluidDensitity,BA_WELL.HoleMinDensity,BA_WELL.HoleMinDensityCasingSize,BA_WELL.HoleMinDensitySlurySystem,BA_WELL.HoleMinDensityFluidDensitity," +
                "BA_WELL.MudLineTemp,BA_WELL.HDVSVD,BA_WELL.MainChallenge from BA_WELL where 1=1");
        List<Object> params=new ArrayList<>();
        if(!workBaseInputDto.getWellName().equals("") && workBaseInputDto.getWellName() != null){
            // sqlBuilder.append(" and BA_WELL.WELLNAME like (?||'%')");//like('%'||?||'%')
            // totalSqlBuilder.append(" and BA_WELL.WELLNAME like (?||'%')");
            // 3.7 fix bug wellName 不能模糊查询
            // wellName 不需要 params.add() 模糊查询不允许
            sqlBuilder.append(" and BA_WELL.WELLNAME like " + "'%" + workBaseInputDto.getWellName() + "%'");
            totalSqlBuilder.append(" and BA_WELL.WELLNAME like"+ "'%" + workBaseInputDto.getWellName() + "%'");
        }

        if(!workBaseInputDto.getWorkCompany().equals("") && workBaseInputDto.getWorkCompany()!= null){
            sqlBuilder.append(" and BA_WELL.cementCompany=?");
            totalSqlBuilder.append(" and BA_WELL.cementCompany=?");
            params.add(workBaseInputDto.getWorkCompany());
        }

        if(!workBaseInputDto.getCountry().equals("") && workBaseInputDto.getCountry() != null){
            sqlBuilder.append(" and BA_WELL.COUNTRY =?");
            totalSqlBuilder.append(" and BA_WELL.COUNTRY =?");
            params.add(workBaseInputDto.getCountry());
        }

        if(!workBaseInputDto.getArea().equals("") && workBaseInputDto.getArea()!= null){
            sqlBuilder.append(" and BA_WELL.AREA=?");
            totalSqlBuilder.append(" and BA_WELL.AREA=?");
            params.add(workBaseInputDto.getArea());
        }

        if(!workBaseInputDto.getBlock().equals("") && workBaseInputDto.getBlock()!= null){
            sqlBuilder.append(" and BA_WELL.BLOCK=?");
            totalSqlBuilder.append(" and BA_WELL.BLOCK=?");
            params.add(workBaseInputDto.getBlock());
        }

        if(!workBaseInputDto.getOilfield().equals("") && workBaseInputDto.getOilfield()!= null){
            sqlBuilder.append(" and BA_WELL.Oilfield=?");
            totalSqlBuilder.append(" and BA_WELL.Oilfield=?");
            params.add(workBaseInputDto.getOilfield());
        }

        if(!workBaseInputDto.getWellType().equals("") && workBaseInputDto.getWellType()!= null){
            sqlBuilder.append(" and BA_WELL.WellType=?");
            totalSqlBuilder.append(" and BA_WELL.WellType=?");
            params.add(workBaseInputDto.getWellType());
        }

        if(!workBaseInputDto.getWellSpacing().equals("") && workBaseInputDto.getWellSpacing()!= null){
            sqlBuilder.append(" and BA_WELL.WellSpacing=?");
            totalSqlBuilder.append(" and BA_WELL.WellSpacing=?");
            params.add(workBaseInputDto.getWellSpacing());
        }

        // TODO: drillDate between date1 and date2
        // TODO: 判断drillDate
        if(!workBaseInputDto.getDrillDate().equals("") && workBaseInputDto.getDrillDate()!= null
                && !workBaseInputDto.getDrillDate().equals("&")) {
            System.out.println("date" + workBaseInputDto.getDrillDate());
            String[] date = workBaseInputDto.getDrillDate().split("&");sqlBuilder.append(" and BA_WELL.DRILLINGSTARTTIME between date " + "'" + date[0] + "' " + "and date " + "'" + date[1] + "'");
            totalSqlBuilder.append("and BA_WELL.DRILLINGSTARTTIME between date " + "'" + date[0] + "' " + "and date " + "'" + date[1] + "'");

            System.out.println("drillDate:"+date[0] + "----"  + date[1]);
        }

        if(!workBaseInputDto.getActualWellDepth().equals("") && workBaseInputDto.getActualWellDepth()!= null){
            sqlBuilder.append(" and BA_WELL.ActualWellDepth>"+workBaseInputDto.getActualWellDepth());
            totalSqlBuilder.append(" and BA_WELL.ActualWellDepth>"+workBaseInputDto.getActualWellDepth());
//            params.add(workBaseInputDto.getActualWellDepth());
            System.out.println("getActualWellDepth:"+workBaseInputDto.getActualWellDepth());

        }

        if(!workBaseInputDto.getHoleTemperature().equals("") && workBaseInputDto.getHoleTemperature()!= null){
            sqlBuilder.append(" and BA_WELL.HoleTemperature>"+workBaseInputDto.getHoleTemperature());
            totalSqlBuilder.append(" and BA_WELL.HoleTemperature>"+workBaseInputDto.getHoleTemperature());
//            params.add(workBaseInputDto.getHoleTemperature());
            System.out.println("getHoleTemperature:"+workBaseInputDto.getHoleTemperature());

        }

        if(!workBaseInputDto.getHolePressure().equals("") && workBaseInputDto.getHolePressure()!= null){
            sqlBuilder.append(" and BA_WELL.HolePressure>"+workBaseInputDto.getHolePressure());
            totalSqlBuilder.append(" and BA_WELL.HolePressure>"+workBaseInputDto.getHolePressure());
//            params.add(workBaseInputDto.getHolePressure());
            System.out.println("HolePressure:"+workBaseInputDto.getHolePressure());

        }

        if(!workBaseInputDto.getHoleMaxDensity().equals("") && workBaseInputDto.getHoleMaxDensity()!= null){
            sqlBuilder.append(" and BA_WELL.HoleMaxDensity>"+workBaseInputDto.getHoleMaxDensity());
            totalSqlBuilder.append(" and BA_WELL.HoleMaxDensity>"+workBaseInputDto.getHoleMaxDensity());
//            params.add(workBaseInputDto.getHoleMaxDensity());
            System.out.println("getHoleMaxDensity:"+workBaseInputDto.getHoleMaxDensity());
        }
//        sqlBuilder.append(" group by BA_WELL.wellName,BA_WELL.country,BA_WELL.area,BA_WELL.block,BA_WELL.oilfield,BA_WELL.geographicalPosition,BA_WELL.structuralLocation,\n" +
//                "BA_WELL.waterDepth,BA_WELL.mudLine,BA_WELL.wellType,BA_WELL.wellSpacing,BA_WELL.cementCompany,BA_WELL.drillingStartTime,\n" +
//                "BA_WELL.drillingFinishTime,CM_WORK_BASE_INFO.actualWellDepth,CM_WORK_BASE_INFO.holeTemperature,CM_WORK_BASE_INFO.holePressure,CM_WORK_BASE_INFO.holeMaxDensity");
        String sql=sqlBuilder.toString();
        System.out.println("sql=" + sql);


        //查询总记录数total
        String totalSql=totalSqlBuilder.toString();
        int total= dbHelper.executeSingleQuery(totalSql,params);
        System.out.println(total);
        int pageSize = pageRequest.getPageSize();
        int page = pageRequest.getPageNumber();
        String data= dbHelper.executeQueryJsonByPage(sql,params, pageSize, page);//params
        if ("]".equals(data)||"[".equals(data)){
            data = "[]";
        }
//        String tempData=data;
//        String[] s=tempData.trim().replace("[","").replace("]","").split("}");
//        int total;
//        if(s[0]==null||s[0].equals("")){
//            total=0;
//        }else{
//            total=s.length;
//        }
//        String jsonStr= JSONHelper.getPageJson(data,total,pageSize,page);
        System.out.println("data=" + data);
        JSONObject jsonobj = new JSONObject();
        JSONArray jarr = JSONArray.fromObject(data);
        jsonobj.put("data",jarr);
        jsonobj.put("total",total);
        jsonobj.put("pagesize",pageSize);
        jsonobj.put("page",page);
        return jsonobj;

    }
}
